/*
 EXEC SP_LOAD_PRODUCT_DETAILS '0','','','',''
*/
alter PROC SP_LOAD_PRODUCT_DETAILS

@PROCESSSTEP_IDS	VARCHAR(50),
@PACKAGE		VARCHAR(20),
@PRODUCTGROUP		VARCHAR(20),
@PINCOUNT		VARCHAR(20),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN
	CREATE TABLE #RESULT_TABLE ( [Planner Code] VARCHAR(50), [Fab Part] VARCHAR(50), [Marketing Part Number] VARCHAR(50), [Manufacturing Part Number] VARCHAR(50),
				[Dies/Package] INT,[MCM BOM P/N] VARCHAR(50), [MCM BOM P/N Qty] INT, [Short Names] VARCHAR (50),[Part Status] VARCHAR(50),
				[Chip Family] VARCHAR(50), [Port] VARCHAR(50), [Chip Set] VARCHAR(50), Package VARCHAR(20), [Package Size] VARCHAR(50),[Pin Count] INT,
				[Wafer Technology] VARCHAR(50),[Wafer Size] VARCHAR(50), [Wafer # Layers] INT,[Fab GDW] INT,
				[Wire to Package Ball ratio] VARCHAR(50),[Substrate Technology] VARCHAR(50), [Substrate Layer] VARCHAR(50),
				[Plan Sort Yield] FLOAT,[Plan Assy Yield] FLOAT,[Plan Test Yield] FLOAT,[Process Yield] FLOAT, [Plan NDW] INT,
				[Fab Cycle Time] FLOAT, [Sort Cycle Time] FLOAT, [Assembly Cycle Time] FLOAT, [Test Cycle Time] FLOAT, [EOL Cycle Time] FLOAT,
				[Cycle Time] FLOAT, [Fab Vendor] varchar(50),[Fab Site] varchar(50), [Wafer Sort Vendor1] varchar(50),[Wafer Sort Vendor2] varchar(50),
				[Assembly Vendor1] varchar(50),[Assembly Vendor2] varchar(50), [Assembly Vendor3] varchar(50),[Assembly Vendor4] varchar(50),
				[Test Vendor1] varchar(50),[Test Vendor2] varchar(50), [Test Vendor3] varchar(50),[Test Vendor4] varchar(50), [Test Time] varchar (30),
				[Tester Platform] VARCHAR(50),[Index Time] VARCHAR(30),[Vendor Tester Cost/Hr] VARCHAR(50),[Consigned Tester/Hr] VARCHAR(50),
				[FG Vendor1] VARCHAR(50),[FG Vendor2] VARCHAR(50), [FG Vendor3] VARCHAR(50),[FG Vendor4] VARCHAR(50),
				[Standard Wafer Cost] NUMERIC(18,4),[Standard Sort Cost] NUMERIC(18,4),[Standard Assy Cost] NUMERIC(18,4),[Standard Test Cost] NUMERIC(18,4), [Standard EOL Cost] NUMERIC(18,4),
				--[Vendor Wafer Cost] NUMERIC(18,4), [Vendor Sort Cost] NUMERIC(18,4), [Vendor Assy Cost] NUMERIC(18,4), [Vendor Test Cost] NUMERIC(18,4), [Vendor EOL Cost] NUMERIC(18,4),	
				[T/R & Tray Qty] INT, [Inner Box Qty] INT, [Carton Qty] INT, [Weight / Box] FLOAT, [ECCN] VARCHAR(50), [HTS Code] VARCHAR(50), [CCATS] VARCHAR(50) )
	
	DECLARE @PART_TABLE TABLE ( PROCESSSTEP_ID INT, PS_TYPE VARCHAR(50), PRODUCT_NAME VARCHAR(50))
	
	DECLARE @SQLSTRING			NVARCHAR(2000)
	DECLARE @FAB_PROCESSSTEP_ID INT
	DECLARE @SORT_PROCESSSTEP_ID INT
	DECLARE @ASSEMBLY_PROCESSSTEP_ID INT
	DECLARE @TEST_PROCESSSTEP_ID INT
	DECLARE @EOL_PROCESSSTEP_ID INT
	DECLARE @FGPROCESSTEP_ID 	INT

	DECLARE @PLANNER_CODE VARCHAR(50), @FAB_PART_NUMBER VARCHAR(50), @MKT_PART_NUMBER VARCHAR(50),@PART_NUMBER VARCHAR(50),
			@MCM_BOM_PART VARCHAR(50), @MCM_BOM_PART_QTY VARCHAR(50), @SHORT_NAMES VARCHAR(50), @PART_STATUS VARCHAR(50), @CHIP_FAMILY VARCHAR(50),
			@CHIP_SET VARCHAR(50), @PACKAGE_TYPE VARCHAR(50), @PACKAGE_SIZE	VARCHAR(50), @Wafer_Technology VARCHAR(50), @Wafer_Size VARCHAR(50),
			@FABVENDOR VARCHAR(50), @SORTVENDOR VARCHAR(50),@SORTVENDOR1 VARCHAR(50),@SORTVENDOR2 VARCHAR(50), @ASSEMBLYVENDOR VARCHAR(50), @TESTVENDOR VARCHAR(50), @FGVENDOR VARCHAR(50),
			@ASSEMBLYVENDOR1 VARCHAR(50),@ASSEMBLYVENDOR2 VARCHAR(50),@ASSEMBLYVENDOR3 VARCHAR(50),@ASSEMBLYVENDOR4 VARCHAR(50),
			@TESTVENDOR1 VARCHAR(50),@TESTVENDOR2 VARCHAR(50),@TESTVENDOR3 VARCHAR(50),@TESTVENDOR4 VARCHAR(50),@TEST_TIME	VARCHAR(50),
			@INDEX_TIME VARCHAR(50), @TESTER_PLATFORM VARCHAR(50),
			@FGVENDOR1 VARCHAR(50),@FGVENDOR2 VARCHAR(50),@FGVENDOR3 VARCHAR(50),@FGVENDOR4 VARCHAR(50), 
			@ECCN VARCHAR(50), @HTS_CODE VARCHAR(50), @CCATS VARCHAR(50), @FAB_SITE VARCHAR(50), @PRODUCT_GROUP VARCHAR(50),
			@WIRE_PKG_RATIO VARCHAR(50), @SUBSTRATE_TECH VARCHAR(50), @SUBSTRATE_LAYER VARCHAR(50)
			

	DECLARE @DIES_PER_PACKAGE INT,@PORT INT, @Wafer_Layers INT, @PIN_COUNT INT, @GROSS_DIE INT, @NET_DIE INT, @TRAY_Qty INT, @Inner_Box_Qty INT, @Cartoon_Qty INT

	DECLARE @PLAN_ASSY_YIELD FLOAT, @PLAN_SORT_YIELD FLOAT, @PLAN_TEST_YIELD FLOAT, @PROCESS_YIELD FLOAT,
			@ASSY_YIELD FLOAT, @SORT_YIELD FLOAT, @TEST_YIELD FLOAT,
			@FAB_CYCLE_TIME FLOAT,@SORT_CYCLE_TIME FLOAT,@ASSEMBLY_CYCLE_TIME FLOAT,@TEST_CYCLE_TIME FLOAT,@EOL_CYCLE_TIME FLOAT,
			@FAB_STD_COST NUMERIC(18,4),@SORT_STD_COST NUMERIC(18,4),@ASSEMBLY_STD_COST NUMERIC(18,4),@TEST_STD_COST NUMERIC(18,4),@EOL_STD_COST NUMERIC(18,4),
			@FAB_VENDOR_COST NUMERIC(18,4),@SORT_VENDOR_COST NUMERIC(18,4),@ASSEMBLY_VENDOR_COST NUMERIC(18,4),@TEST_VENDOR_COST NUMERIC(18,4),@EOL_VENDOR_COST NUMERIC(18,4),
			@VENDOR_TESTER_COST_HR NUMERIC(18,4), @CONSIGNED_TESTER_COST_HR NUMERIC(18,4),
			@WEIGHT_BOX FLOAT

	DECLARE @FAB_CURSOR CURSOR
	DECLARE @SORT_CURSOR CURSOR
	DECLARE @ASSEMBLY_CURSOR CURSOR	
	DECLARE @TEST_CURSOR CURSOR	
	DECLARE @FG_CURSOR CURSOR

	DECLARE @COUNT 		INT
	DECLARE @QUARTER_NO INT
	DECLARE @QTR_NO_STR VARCHAR(2)
	DECLARE @YEAR VARCHAr(4)
	DECLARE @YEAR_NO VARCHAR(2)

	DECLARE @PRODUCTFLOW_CURSOR CURSOR
	
	DECLARE @SQL NVARCHAR(MAX) -- alternate nvarchar(1024)

	
	SET @QUARTER_NO = 1
	SELECT @QUARTER_NO = CQC_Quarter_No FROM Company_Quarterly_Calendar WHERE CONVERT( VARCHAR, Current_Timestamp, 101 ) >= CQC_Start_Date and CONVERT( VARCHAR, Current_Timestamp, 101 ) <= CQC_End_Date

	SET @QTR_NO_STR = @QUARTER_NO + ''
	SET @YEAR = YEAR(CURRENT_TIMESTAMP)
	SET @YEAR_NO = SUBSTRING( @YEAR, LEN(@YEAR)-1, LEN(@YEAR) )
	--SET @SQLSTRING = N'
	IF @PROCESSSTEP_IDS IS NOT NULL AND @PROCESSSTEP_IDS <> '0'
	BEGIN 
		SET @PRODUCTFLOW_CURSOR = CURSOR FOR 
		SELECT P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER,PART_NUMBER,PART_STATUS, PRODUCT_GROUP FROM PRODUCT P,PROCESS_STEP PS 
		WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'FG' AND P_MARKETING_PART_NUMBER IS NOT NULL AND P.PROCESSSTEP_ID = @PROCESSSTEP_IDS
	END
	ELSE
	BEGIN
		SET @PRODUCTFLOW_CURSOR = CURSOR FOR 
		SELECT P.PROCESSSTEP_ID,P_MARKETING_PART_NUMBER, PART_NUMBER,PART_STATUS, PRODUCT_GROUP FROM PRODUCT P,PROCESS_STEP PS 
		WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS_TYPE = 'FG' AND P_MARKETING_PART_NUMBER IS NOT NULL  
	END 
		
	OPEN @PRODUCTFLOW_CURSOR
	FETCH NEXT FROM @PRODUCTFLOW_CURSOR INTO @FGPROCESSTEP_ID,@MKT_PART_NUMBER,@PART_NUMBER,@PART_STATUS,@PRODUCT_GROUP
	WHILE @@FETCH_STATUS = 0
	BEGIN
		
		INSERT INTO @PART_TABLE 
		SELECT ProcessStep_Id,PS_TYPE,PRODUCT_NAME from FN_GET_PROCESS_FLOW_By_ProcessStep_Id ( @FGPROCESSTEP_ID )
				
		SELECT @FAB_PART_NUMBER = PRODUCT_NAME, @FAB_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'FAB'
		SELECT @SORT_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'SORT'
		SELECT @ASSEMBLY_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'ASSEMBLY'
		SELECT @EOL_PROCESSSTEP_ID = PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'EOL'
		SELECT @TEST_PROCESSSTEP_ID	= PROCESSSTEP_ID FROM @PART_TABLE WHERE PS_TYPE = 'FT'

		-- FAB
		SELECT @GROSS_DIE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Gross Die/Wafer' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID 
		SELECT @NET_DIE  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Plan NDW' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT @Wafer_Technology  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Fab Process' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT @Wafer_Size  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Wafer Size' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT @Wafer_Layers  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Wafer Layers' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT @FAB_STD_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT @FAB_VENDOR_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Vendor Unit Cost' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT @FAB_SITE  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Fab Site' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		--SELECT TOP 1 @FABVENDOR = L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		SELECT @FAB_CYCLE_TIME  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @FAB_PROCESSSTEP_ID
		
		-- SORT
		--SELECT TOP 1 @SORTVENDOR = L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID
		--SELECT @SORT_STD_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID
		SELECT @SORT_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
					WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
					WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
					ELSE Q4_STANDARD_COST
				 END), @PLAN_SORT_YIELD = (CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
					WHEN @QUARTER_NO = 2 THEN Q2_YIELD
					WHEN @QUARTER_NO = 3 THEN Q3_YIELD
					ELSE Q4_YIELD
				 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
		--SELECT @SORT_VENDOR_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Vendor Unit Cost' AND PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID
		SELECT @SORT_CYCLE_TIME  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID
		--SELECT @PLAN_SORT_YIELD  = ISNULL(PARAMETER_VALUE,0.0) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID
		
		-- ASSEMBLY
		SELECT @PACKAGE_TYPE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PACKAGE ID' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID  	
		SELECT @PIN_COUNT = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'PIN' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID 
		SELECT @PACKAGE_SIZE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Package Size' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID

		SELECT @WIRE_PKG_RATIO = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Wire to Package Ball ratio' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID 
		SELECT @SUBSTRATE_TECH = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Substrate Technology' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID 
		SELECT @SUBSTRATE_LAYER = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Substrate Layer' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID 

		--SELECT @ASSEMBLY_STD_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		SELECT @ASSEMBLY_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
					WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
					WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
					ELSE Q4_STANDARD_COST
				 END) , @PLAN_ASSY_YIELD = (CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
					WHEN @QUARTER_NO = 2 THEN Q2_YIELD
					WHEN @QUARTER_NO = 3 THEN Q3_YIELD
					ELSE Q4_YIELD
				 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @EOL_PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR
		--SELECT @ASSEMBLY_VENDOR_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Vendor Unit Cost' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		SELECT @ASSEMBLY_CYCLE_TIME  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		--SELECT @PLAN_ASSY_YIELD  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		SELECT @DIES_PER_PACKAGE  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Dies/Package' AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		SELECT TOP 1 @MCM_BOM_PART = P.PART_NUMBER, @MCM_BOM_PART_QTY = ABST.Qty FROM Assembly_BOM_SETUP_TABLE ABST, Product P WHERE ABST.bom_Processstep_ID = P.ProcessStep_ID AND ABST.assembly_Processstep_ID = @ASSEMBLY_PROCESSSTEP_ID

		-- TEST		
		SELECT @TEST_TIME = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'TEST TIME' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 
		SELECT @INDEX_TIME = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'INDEX TIME' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 
		SELECT @TESTER_PLATFORM = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'TESTER PLATFORM' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 
		SELECT @VENDOR_TESTER_COST_HR = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'VENDOR TESTER COST' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 
		SELECT @CONSIGNED_TESTER_COST_HR = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'CONSIGNED TESTER COST' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID 

		
		--SELECT @TEST_STD_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID
		SELECT @TEST_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
					WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
					WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
					ELSE Q4_STANDARD_COST
				 END) , @PLAN_TEST_YIELD = (CASE WHEN @QUARTER_NO = 1 THEN Q1_YIELD
					WHEN @QUARTER_NO = 2 THEN Q2_YIELD
					WHEN @QUARTER_NO = 3 THEN Q3_YIELD
					ELSE Q4_YIELD
				 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR

		--SELECT @TEST_VENDOR_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Vendor Unit Cost' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID
		SELECT @TEST_CYCLE_TIME  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID
		--SELECT @PLAN_TEST_YIELD  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID
		
		-- EOL
		--SELECT @EOL_STD_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Unit Cost' AND PROCESSSTEP_ID = @EOL_PROCESSSTEP_ID
		SELECT @EOL_STD_COST = (CASE WHEN @QUARTER_NO = 1 THEN Q1_STANDARD_COST
					WHEN @QUARTER_NO = 2 THEN Q2_STANDARD_COST
					WHEN @QUARTER_NO = 3 THEN Q3_STANDARD_COST
					ELSE Q4_STANDARD_COST
				 END) FROM Update_view_cost_table WHERE PROCESSSTEP_ID = @EOL_PROCESSSTEP_ID AND YEAR(Fin_Year) = @YEAR

		--SELECT @EOL_VENDOR_COST  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Vendor Unit Cost' AND PROCESSSTEP_ID = @EOL_PROCESSSTEP_ID
		SELECT @EOL_CYCLE_TIME  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @EOL_PROCESSSTEP_ID
		--SELECT @PLAN_EOL_YIELD  = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Target Yield' AND PROCESSSTEP_ID = @EOL_PROCESSSTEP_ID
		
		-- FG
		SELECT @PLANNER_CODE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Planner' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @SHORT_NAMES = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Short Name' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @PART_STATUS = PART_STATUS FROM PRODUCT WHERE ProcessStep_ID = @FGPROCESSTEP_ID
		SELECT @CHIP_FAMILY = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Chip Family' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @PORT = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Port' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @CHIP_SET = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Chipset1' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @ECCN = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'ECCN' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @HTS_CODE = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Harmonized Traffic Code' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @CCATS = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'CCATS' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @TRAY_Qty = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Units Per Tray' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @Inner_Box_Qty = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Trays Per InnerBox' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @Cartoon_Qty = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'InnerBoxes Per OuterBox' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		SELECT @WEIGHT_BOX = PARAMETER_VALUE FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Wieght per Box' AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		
		IF @PLAN_SORT_YIELD = 0.0000 OR @PLAN_SORT_YIELD = 0.0
		BEGIN
				SET @SORT_YIELD = 100
		END
		ELSE
		BEGIN
				SET @SORT_YIELD = @PLAN_SORT_YIELD
		END
		IF @PLAN_ASSY_YIELD = 0.0000 OR @PLAN_ASSY_YIELD = 0.0
		BEGIN
				SET @ASSY_YIELD = 100
		END
		ELSE
		BEGIN
				SET @ASSY_YIELD = @PLAN_ASSY_YIELD
		END
		
		IF @PLAN_TEST_YIELD = 0.0000 OR @PLAN_TEST_YIELD = 0.0
		BEGIN
				SET @TEST_YIELD = 100
		END
		ELSE
		BEGIN
				SET @TEST_YIELD = @PLAN_TEST_YIELD 
		END
		SET @PROCESS_YIELD = ( ( (ISNULL(@SORT_YIELD,100)*ISNULl(@ASSY_YIELD,100)*iSNULl(@TEST_YIELD,100))/100 )/100 )

		SET @COUNT = 1
		
		SET @SORT_CURSOR = CURSOR FOR 
			SELECT distinct L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @SORT_PROCESSSTEP_ID
		OPEN @SORT_CURSOR
		FETCH NEXT FROM @SORT_CURSOR INTO @SORTVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			if @COUNT = 1
			BEGIN
				SET @SORTVENDOR1 = @SORTVENDOR
			END
			ELSE IF @COUNT = 2
			BEGIN
				SET @SORTVENDOR2 = @SORTVENDOR
			END
			set @COUNT = @COUNT + 1

			FETCH NEXT FROM @SORT_CURSOR INTO @SORTVENDOR
		END
		
		CLOSE @SORT_CURSOR
		DEALLOCATE @SORT_CURSOR

 		SET @COUNT = 1			
		SET @ASSEMBLY_CURSOR = CURSOR FOR 
			SELECT distinct L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @ASSEMBLY_PROCESSSTEP_ID
		OPEN @ASSEMBLY_CURSOR
		FETCH NEXT FROM @ASSEMBLY_CURSOR INTO @ASSEMBLYVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			if @COUNT = 1
			BEGIN
				SET @ASSEMBLYVENDOR1 = @ASSEMBLYVENDOR
			END
			ELSE IF @COUNT = 2
			BEGIN
				SET @ASSEMBLYVENDOR2 = @ASSEMBLYVENDOR
			END
			/*ELSE IF @COUNT = 3
			BEGIN
				SET @ASSEMBLYVENDOR3 = @ASSEMBLYVENDOR
			END
			ELSE IF @COUNT = 4
			BEGIN
				SET @ASSEMBLYVENDOR4 = @ASSEMBLYVENDOR
			END */
			set @COUNT = @COUNT + 1

			FETCH NEXT FROM @ASSEMBLY_CURSOR INTO @ASSEMBLYVENDOR
		END
		
		CLOSE @ASSEMBLY_CURSOR
		DEALLOCATE @ASSEMBLY_CURSOR

		SET @COUNT = 1
		SET @TEST_CURSOR = CURSOR FOR 
			SELECT distinct L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @TEST_PROCESSSTEP_ID
		OPEN @TEST_CURSOR
		FETCH NEXT FROM @TEST_CURSOR INTO @TESTVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			if @COUNT = 1
			BEGIN
				SET @TESTVENDOR1 = @TESTVENDOR
			END
			ELSE IF @COUNT = 2
			BEGIN
				SET @TESTVENDOR2 = @TESTVENDOR
			END
			/*ELSE IF @COUNT = 3
			BEGIN
				SET @TESTVENDOR3 = @TESTVENDOR
			END
			ELSE IF @COUNT = 4
			BEGIN
				SET @TESTVENDOR4 = @TESTVENDOR
			END */
				SET @COUNT = @COUNT + 1

			FETCH NEXT FROM @TEST_CURSOR INTO @TESTVENDOR
		END
		
		CLOSE @TEST_CURSOR
		DEALLOCATE @TEST_CURSOR	
		
		SET @COUNT = 1
		SET @FG_CURSOR = CURSOR FOR 
			SELECT distinct L_VENDOR_NAME FROM PROCESS_STEP_LOCATION_TABLE PSLT, LOCATION L WHERE PSLT.LOCATION_ID = L.LOCATION_ID AND PROCESSSTEP_ID = @FGPROCESSTEP_ID
		OPEN @FG_CURSOR
		FETCH NEXT FROM @FG_CURSOR INTO @FGVENDOR
		WHILE @@FETCH_STATUS = 0
		BEGIN	
			if @COUNT = 1
			BEGIN
				SET @FGVENDOR1 = @FGVENDOR
			END
			ELSE IF @COUNT = 2
			BEGIN
				SET @FGVENDOR2 = @FGVENDOR
			END
			/*ELSE IF @COUNT = 3
			BEGIN
				SET @FGVENDOR3 = @FGVENDOR
			END
			ELSE IF @COUNT = 4
			BEGIN
				SET @FGVENDOR4 = @FGVENDOR
			END */
			set @COUNT = @COUNT + 1

			FETCH NEXT FROM @FG_CURSOR INTO @FGVENDOR
		END
		
		CLOSE @FG_CURSOR
		DEALLOCATE @FG_CURSOR	

		INSERT INTO #RESULT_TABLE ( [Planner Code], [Fab Part], [Marketing Part Number], [Manufacturing Part Number],
				[Dies/Package],[MCM BOM P/N], [MCM BOM P/N Qty], [Short Names],[Part Status],
				[Chip Family], [Port], [Chip Set], Package , [Package Size],[Pin Count],
				[Wafer Technology],[Wafer Size], [Wafer # Layers],[Fab GDW],
				[Plan Sort Yield],[Plan Assy Yield],[Plan Test Yield],[Process Yield], [Plan NDW] ,
				[Fab Cycle Time] , [Sort Cycle Time], [Assembly Cycle Time], [Test Cycle Time], [EOL Cycle Time],
				[Cycle Time], /*[Fab Vendor],[Fab Site],*/ [Wafer Sort Vendor1],[Wafer Sort Vendor2],
				[Assembly Vendor1],[Assembly Vendor2], [Wire to Package Ball ratio],[Substrate Technology], [Substrate Layer],
				[Test Vendor1],[Test Vendor2],[Tester Platform],[Test Time],[Index Time],[Vendor Tester Cost/Hr],[Consigned Tester/Hr],
				[FG Vendor1],[FG Vendor2],
				[Standard Wafer Cost],[Standard Sort Cost],[Standard Assy Cost],[Standard Test Cost], [Standard EOL Cost],
				--[Vendor Wafer Cost], [Vendor Sort Cost], [Vendor Assy Cost], [Vendor Test Cost], [Vendor EOL Cost],	
				[T/R & Tray Qty],	[Inner Box Qty],[Carton Qty], [Weight / Box],	[ECCN],	[HTS Code],	[CCATS] )

		VALUES ( @PLANNER_CODE, @FAB_PART_NUMBER, @MKT_PART_NUMBER, @PART_NUMBER, @DIES_PER_PACKAGE, @MCM_BOM_PART, @MCM_BOM_PART_QTY,
				@SHORT_NAMES, @PART_STATUS,@CHIP_FAMILY, @PORT, @CHIP_SET, @PACKAGE_TYPE, @PACKAGE_SIZE, @PIN_COUNT, @Wafer_Technology, @Wafer_Size,
				@Wafer_Layers, @GROSS_DIE, (@PLAN_SORT_YIELD/100), (@PLAN_ASSY_YIELD/100), (@PLAN_TEST_YIELD/100), (@PROCESS_YIELD/100) ,
				 @NET_DIE, @FAB_CYCLE_TIME, @SORT_CYCLE_TIME, @ASSEMBLY_CYCLE_TIME, @TEST_CYCLE_TIME, @EOL_CYCLE_TIME,
			    (ISNULL(@FAB_CYCLE_TIME, 0)+ISNULL( @SORT_CYCLE_TIME, 0 )+ ISNULL( @ASSEMBLY_CYCLE_TIME, 0 )+ ISNULL( @TEST_CYCLE_TIME, 0 )+ ISNULL( @EOL_CYCLE_TIME, 0 ) )/7,
				/*@FABVENDOR, @FAB_SITE,*/ @SORTVENDOR1, @SORTVENDOR2,
				@ASSEMBLYVENDOR1,@ASSEMBLYVENDOR2,@WIRE_PKG_RATIO, @SUBSTRATE_TECH, @SUBSTRATE_LAYER,
				@TESTVENDOR1,@TESTVENDOR2,@TESTER_PLATFORM, @TEST_TIME,@INDEX_TIME,@VENDOR_TESTER_COST_HR,@CONSIGNED_TESTER_COST_HR,
				@FGVENDOR1,@FGVENDOR2, 
				@FAB_STD_COST, @SORT_STD_COST, @ASSEMBLY_STD_COST, @TEST_STD_COST, @EOL_STD_COST,
				--@FAB_VENDOR_COST, @SORT_VENDOR_COST, @ASSEMBLY_VENDOR_COST, @TEST_VENDOR_COST, @EOL_VENDOR_COST, 
				@TRAY_Qty, @Inner_Box_Qty, @Cartoon_Qty, @WEIGHT_BOX, @ECCN, @HTS_CODE, @CCATS )
						
				SET @PLANNER_CODE  = NULL 
				SET  @FAB_PART_NUMBER  = NULL 
				SET  @MKT_PART_NUMBER  = NULL 
				SET @PART_NUMBER  = NULL 
				SET  @DIES_PER_PACKAGE  = NULL 
				SET @MCM_BOM_PART  = NULL 
				SET  @MCM_BOM_PART_QTY  = NULL 
				SET  @SHORT_NAMES  = NULL 
				SET  @PART_STATUS  = NULL 
				SET  @CHIP_FAMILY  = NULL 
				SET  @PORT  = NULL 
				SET @CHIP_SET  = NULL 
				SET  @PACKAGE_TYPE  = NULL 
				SET  @PACKAGE_SIZE	 = NULL 
				SET  @PIN_COUNT	 = NULL 
				SET  @Wafer_Technology  = NULL 
				SET  @Wafer_Size  = NULL 
				SET @Wafer_Layers  = NULL 
				SET  @FABVENDOR  = NULL 
				SET  @SORTVENDOR  = NULL 
				SET  @ASSEMBLYVENDOR  = NULL 
				SET  @TESTVENDOR  = NULL 
				SET  @FGVENDOR  = NULL 
				SET @ASSEMBLYVENDOR1  = NULL 
				SET @ASSEMBLYVENDOR2  = NULL 
				SET @ASSEMBLYVENDOR3  = NULL 
				SET @ASSEMBLYVENDOR4  = NULL 
				SET @WIRE_PKG_RATIO = NULL
				SET @SUBSTRATE_TECH = NULL
				SET @SUBSTRATE_LAYER = NULL
				SET @TESTVENDOR1  = NULL 
				SET @TESTVENDOR2  = NULL 
				SET @TESTVENDOR3  = NULL 
				SET @TESTVENDOR4  = NULL 
				SET @FGVENDOR1  = NULL 
				SET @FGVENDOR2  = NULL 
				SET @FGVENDOR3  = NULL 
				SET @FGVENDOR4  = NULL 
				SET @TEST_TIME	 = NULL 
				SET @TESTER_PLATFORM = NULL
				SET @INDEX_TIME = NULL
				SET @VENDOR_TESTER_COST_HR = NULL 
				SET @CONSIGNED_TESTER_COST_HR = NULL
				SET @ECCN  = NULL 
				SET @HTS_CODE  = NULL 
				SET @CCATS  = NULL 
				SET @FAB_SITE  = NULL 
				SET @PRODUCT_GROUP  = NULL 
				SET @GROSS_DIE = 0
				SET @NET_DIE = 0 
				SET @TRAY_Qty = 0 
				SET @Inner_Box_Qty = 0 
				SET @Cartoon_Qty = 0
				SET @PLAN_ASSY_YIELD = 0.0 
				SET  @PLAN_SORT_YIELD= 0.0 
				SET  @PLAN_TEST_YIELD= 0.0 
				SET @FAB_CYCLE_TIME = 0.0 
				SET @SORT_CYCLE_TIME=  0.0
				SET @ASSEMBLY_CYCLE_TIME= 0.0
				SET @TEST_CYCLE_TIME= 0.0
				SET @EOL_CYCLE_TIME= 0.0 
				SET @FAB_STD_COST= 0.0
				SET @SORT_STD_COST= 0.0
				SET @ASSEMBLY_STD_COST= 0.0
				SET @TEST_STD_COST= 0.0
				SET @EOL_STD_COST= 0.0 
				SET @FAB_VENDOR_COST= 0.0
				SET @SORT_VENDOR_COST= 0.0
				SET @ASSEMBLY_VENDOR_COST= 0.0
				SET @TEST_VENDOR_COST= 0.0
				SET @EOL_VENDOR_COST= 0.0 
				SET @WEIGHT_BOX =  0.0 
		DELETE from @PART_TABLE

	FETCH NEXT FROM @PRODUCTFLOW_CURSOR INTO @FGPROCESSTEP_ID,@MKT_PART_NUMBER,@PART_NUMBER,@PART_STATUS,@PRODUCT_GROUP
	END
	CLOSE @PRODUCTFLOW_CURSOR
	DEALLOCATE @PRODUCTFLOW_CURSOR	

	SET @SQL = 'SELECT [Fab Part], [Manufacturing Part Number],[Marketing Part Number],[Planner Code],[Dies/Package], [MCM BOM P/N] As [MCM Part], [MCM BOM P/N Qty] As [MCM Qty],
				[Part Status], [Chip Family], [Port], [Chip Set], [Wafer Sort Vendor1], [Wafer Sort Vendor2], Package, [Package Size],[Pin Count],
				[Wire to Package Ball ratio],[Substrate Technology], [Substrate Layer],
				[Assembly Vendor1],[Assembly Vendor2],[Tester Platform],[Test Time],[Index Time],[Vendor Tester Cost/Hr],[Consigned Tester/Hr],
				[Test Vendor1],[Test Vendor2],[FG Vendor1],[FG Vendor2],
				[Standard Sort Cost] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Sort Cost], [Standard Assy Cost] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Assy Cost],
				[Standard Test Cost] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Test Cost], [Standard EOL Cost] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' EOL Cost],
				[Fab GDW] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' GDPW], 
				[Plan Sort Yield] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Sort Yield], [Plan Assy Yield] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Assy Yield],
				[Plan Test Yield] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Test Yield], [Process Yield] AS [ Q' + @QTR_NO_STR + @YEAR_NO +' Final Yield], 
				[Fab Cycle Time], [Sort Cycle Time], [Assembly Cycle Time], [Test Cycle Time], [EOL Cycle Time],[Cycle Time],
				[T/R & Tray Qty], [Inner Box Qty], [Carton Qty], [Weight / Box], [ECCN], [HTS Code], [CCATS]

	FROM #RESULT_TABLE ORDER BY  [Marketing Part Number] '
	
	EXEC sp_executeSQL @SQL

END


